# -*- coding: utf-8 -*-
import sys
sys.path.append("..")

from MysqlCon import MysqlCon
import time


class MallSP(object):
    """
    商场-搜铺-1229数据的导入

    1、读取文件数据
    2、写入库表
    """


    #多行插入
    def insert_many(self, connect, cursor, insert_data):
        # 获取数据，写入数据库
        insert_sql = """
        insert into ori_mall_info_sp_20201229(
            platform, url_ID, mall_name, ori_city, ori_county, ori_lnglat, project_img, business_type, is_chain, commercial_area, business_building_area, business_leaseable_area, openning_time, floor_info, address, parking_number, mall_overview, is_product_line, product_line_name, developer, operator, developer_group, operator_group, property_model, rent_model, is_listed_group, floor_plan, shop_number, project_status, business_grade, featured_themes, floor_height, column_distance, hydropower, flue, mall_phone, investment_status, investment_demand, complex_score, facility_score, environment_score, service_score, reviews_number, good_reviews_number, bad_reviews_number, business_time, parking_fee_rules, per_price, first_floor_rent, around_house_rent, around_shop_rent, annual_rent, annual_sale, gd_city, gd_county, gd_lnglat, gd_name, is_precise_name
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        connect.ping(reconnect = True)

        # 批量插入
        try:
            res = cursor.executemany(insert_sql, insert_data)
            print("res = ", res)
            # 提交，不进行提交无法保存到数据库
            connect.commit()
        except Exception as e:
            print(e)
            connect.rollback()
        #finally:
        #connect.close()

        return True


    #生成行数据列表
    def makeDataRow(self, line):
        #分解列值
        cols = line.split("\t")
        colNum = len(cols)
        #print("列数：", colNum)
        rowData = []
        i = 0
        while i < colNum :
            rowData.append(cols[i])
            i += 1
        return rowData



if __name__ == "__main__":

    # 获取数据库连接
    mysql_con = MysqlCon()
    connect_odsdb, cursor_odsdb = mysql_con.con_odsdb()

    #记录执行开始时间
    str_time = int(time.time())

    #实例化类
    mall = MallSP()

    # 读取原文件
    with open('D:\\usr\\EJU\\数据部门\\大数据系统\\商业客户数据部\\数据迁移\\待入库文件20210111\\商场-搜铺-1229.txt','r',encoding='utf-8') as f:
        lines = f.readlines()
        #print(text)
    #跳过第一行表头
    header = lines.pop(0)
    print(header)

    maxRow = len(lines)
    print("总记录数", maxRow)

    row = 0
    insertDataList = []
    for line in lines:
        #分解列值
        rowData = mall.makeDataRow(line)
        insertDataList.append(rowData)
        row = row + 1
        if row > 0 and (row % 1000 == 0 or row == maxRow):
            print(row)
            # 批量写入1000条数据
            mall.insert_many(connect_odsdb, cursor_odsdb, insertDataList)
            insertDataList.clear()

    connect_odsdb.close()

    end_time = int(time.time())
    print("程序执行时间（秒）：", end_time - str_time)
